package com.lovo.roomProject.classManager.dao.impl;

import com.lovo.roomProject.classManager.dao.ClassDAO;
import com.lovo.roomProject.dao.BaseDAO;
import com.lovo.roomProject.pojo.ClaszDO;
import com.lovo.roomProject.pojo.CutPageBean;
import com.lovo.roomProject.pojo.RoomDO;
import com.lovo.roomProject.pojo.StudentDO;

import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class ClassDAOImpl extends BaseDAO implements ClassDAO {
    @Override
    public List listClassManager() {
        this.getConnection();
        String sql = "select *from  t_class ";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            rs = statement.executeQuery();
            while (rs.next()) {
                ClaszDO claszDO = new ClaszDO();
                claszDO.setId(rs.getInt("c_id"));
                claszDO.setName(rs.getString("c_name"));
                claszDO.setTeacher(rs.getString("c_teacher"));
                claszDO.setDate(LocalDate.parse(rs.getString("c_create_date")));

                list.add(claszDO);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public void insert(ClaszDO claszDO) {

        this.getConnection();
        String sql = "insert into t_class (c_name,c_teacher,c_create_date) " +
                " VALUES(?,?,?)";

        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, claszDO.getName());
            statement.setObject(2, claszDO.getTeacher());
            statement.setObject(3, claszDO.getDate().toString());
            statement.executeUpdate();

        } catch (SQLException throwables) {

            throwables.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }


    }

    @Override
    public void delete(int id) {
        this.getConnection();
        String sql = "delete from t_class where c_id=?";

        try {
            statement = con.prepareStatement(sql);

            statement.setObject(1, id);

            statement.executeUpdate();

        } catch (SQLException throwables) {

            throwables.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }

    /**
     * 通过班级名字查询
     *
     * @param name
     * @return
     */
    @Override
    public ClaszDO getClassByName(String name) {
        this.getConnection();
        String sql = "select * from t_class where c_name=?";
        ClaszDO claszDO = null;
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, name);
            rs = statement.executeQuery();
            if (rs.next()) {
                claszDO = new ClaszDO();
                claszDO.setName(rs.getString("c_name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return claszDO;
    }


    @Override
    public CutPageBean listClassByPage(int current) {
        this.getConnection();
        String sql = "select * from t_class";
        String sqlCount = "select count(*) cnt";
        String fromSql = " from t_class ";
        String limitSql = " limit ?,?";

        CutPageBean cutPageBean = new CutPageBean();
        List list = new ArrayList();

        try {
            statement = con.prepareStatement(sql + limitSql);
            statement.setObject(1, (current - 1) * CutPageBean.PAGESIZE);
            statement.setObject(2, CutPageBean.PAGESIZE);
            rs = statement.executeQuery();
            while (rs.next()) {
                ClaszDO claszDO = new ClaszDO();
                claszDO.setId(rs.getInt("c_id"));
                claszDO.setName(rs.getString("c_name"));
                claszDO.setTeacher(rs.getString("c_teacher"));
                claszDO.setDate(LocalDate.parse(rs.getString("c_create_date")));

                list.add(claszDO);
            }
            cutPageBean.setList(list);

            statement = con.prepareStatement(sqlCount + fromSql);
            rs = statement.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }

        return cutPageBean;
    }

    /**
     * 查询班级人数
     * @param id 班级ID
     * @return
     */
    @Override
    public int getClassCount(int id) {
        this.getConnection();
        ClaszDO claszDO = null;
        String sql = "select c.*, count(s.s_name) from t_class c  left join t_student s on c.c_id=s.s_c_id  " +
                " WHERE c.c_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            rs=statement.executeQuery();

            if (rs.next()){
                claszDO=new ClaszDO();
                claszDO.setInCount(rs.getInt("count(s.s_name)"));

            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return claszDO.getInCount();
    }


//    public static void main(String[] args) {
//        ClassDAO classDAO=new ClassDAOImpl();
//        System.out.println(classDAO.listStudentByPage(1));
//    }

}
